package swd.deploy.menu_tree_maintain;

import com.pub.database.*;
import java.util.*;

public class Business {
   QueryBean gqbnDAO=null;
   public Business(QueryBean pqbnDAO){
      gqbnDAO=pqbnDAO;
   }
   String selectTableDataToFld(View view) throws Exception {
      String lstrSQL=null;
      Hashtable<String,Object> lhtTableData=null;
      try{
         lstrSQL="select url,flag,seqno,sys_no,sec_ctl,sec_lev,type_id,type_up," +
                 "       type_mgr,app_empno,flow_send,type_dept,type_desc,type_name," +
                 "       type_path,type_eddate,type_name_e,type_status,type_stdate" +
                 "  from wkf_menu " +
                 " where sys_no='" + view.getsys_no() + "'" +
                 "   and type_id='" + view.gettype_id() + "'" +
                 "   and type_up='" + view.gettype_up() + "'";
         lhtTableData=gqbnDAO.readSingleRowData(lstrSQL);
         if(lhtTableData==null){
            return "查無資料";
         }
         view.seturl(String.valueOf((lhtTableData.get("URL").toString())));
         view.setflag(String.valueOf((lhtTableData.get("FLAG").toString())));
         view.setseqno(String.valueOf((lhtTableData.get("SEQNO").toString())));
         view.setsys_no(String.valueOf((lhtTableData.get("SYS_NO").toString())));
         view.setsec_ctl(String.valueOf((lhtTableData.get("SEC_CTL").toString())));
         view.setsec_lev(String.valueOf((lhtTableData.get("SEC_LEV").toString())));
         view.settype_id(String.valueOf((lhtTableData.get("TYPE_ID").toString())));
         view.settype_up(String.valueOf((lhtTableData.get("TYPE_UP").toString())));
         view.settype_mgr(String.valueOf((lhtTableData.get("TYPE_MGR").toString())));
         view.setapp_empno(String.valueOf((lhtTableData.get("APP_EMPNO").toString())));
         view.setflow_send(String.valueOf((lhtTableData.get("FLOW_SEND").toString())));
         view.settype_dept(String.valueOf((lhtTableData.get("TYPE_DEPT").toString())));
         view.settype_desc(String.valueOf((lhtTableData.get("TYPE_DESC").toString())));
         view.settype_name(String.valueOf((lhtTableData.get("TYPE_NAME").toString())));
         view.settype_path(String.valueOf((lhtTableData.get("TYPE_PATH").toString())));
         view.settype_eddate(String.valueOf((lhtTableData.get("TYPE_EDDATE").toString())));
         view.settype_name_e(String.valueOf((lhtTableData.get("TYPE_NAME_E").toString())));
         view.settype_status(String.valueOf((lhtTableData.get("TYPE_STATUS").toString())));
         view.settype_stdate(String.valueOf((lhtTableData.get("TYPE_STDATE").toString())));

         return "";
      }
      catch(Exception e){
         throw new Exception(e.toString());
      }
      finally{
         lhtTableData.clear();
         lhtTableData=null;
      }
   }
   String exeUpdSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      String strTypePath="";
      try{
         lvtrParam.add(view.geturl()==null ? null :String.valueOf(view.geturl()));
         lvtrParam.add(view.getflag()==null ? null :String.valueOf(view.getflag()));
         lvtrParam.add(view.getseqno()==null ? null :String.valueOf(view.getseqno()));
         lvtrParam.add(view.getsys_no()==null ? null :String.valueOf(view.getsys_no()));
         lvtrParam.add(view.getsec_ctl()==null ? null :String.valueOf(view.getsec_ctl()));
         lvtrParam.add(view.getsec_lev()==null ? null :String.valueOf(view.getsec_lev()));
         lvtrParam.add(view.gettype_id()==null ? null :String.valueOf(view.gettype_id()));
         lvtrParam.add(view.gettype_up()==null ? null :String.valueOf(view.gettype_up()));
         lvtrParam.add(view.gettype_mgr()==null ? null :String.valueOf(view.gettype_mgr()));
         lvtrParam.add(view.getapp_empno()==null ? null :String.valueOf(view.getapp_empno()));
         lvtrParam.add(view.getflow_send()==null ? null :String.valueOf(view.getflow_send()));
         lvtrParam.add(view.gettype_dept()==null ? null :String.valueOf(view.gettype_dept()));
         lvtrParam.add(view.gettype_desc()==null ? null :String.valueOf(view.gettype_desc()));
         lvtrParam.add(view.gettype_name()==null ? null :String.valueOf(view.gettype_name()));
         strTypePath=getTypePathData(view.getsys_no(),view.gettype_up(),view.getflag(),
                                     view.getseqno(),view.gettype_id());
         lvtrParam.add(strTypePath);
         lvtrParam.add(view.gettype_eddate()==null ? null :String.valueOf(view.gettype_eddate()));
         lvtrParam.add(view.gettype_name_e()==null ? null :String.valueOf(view.gettype_name_e()));
         lvtrParam.add(view.gettype_status()==null ? null :String.valueOf(view.gettype_status()));
         lvtrParam.add(view.gettype_stdate()==null ? null :String.valueOf(view.gettype_stdate()));
         lstrSQL="update wkf_menu set url=?,flag=?,seqno=?,sys_no=?,sec_ctl=?,sec_lev=?," +
                 "       type_id=?,type_up=?,type_mgr=?,app_empno=?,flow_send=?,type_dept=?," +
                 "       type_desc=?,type_name=?,type_path=?,type_eddate=?,type_name_e=?," +
                 "       type_status=?,type_stdate=? " +
                 " where sys_no='" + view.getsys_no() + "'" +
                 "   and type_id='" + view.gettype_id() + "'" +
                 "   and type_up='" + view.gettype_up() + "'";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){
            return "wkf_menu";
         }
         return "OK!";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e.toString());
      }
      finally{
         lvtrParam.clear();
      }
   }
   String exeInsSQL(View view) throws Exception{
      String lstrSQL=new String();
      String strTypePath="";
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add((view.getsys_no()==null ? null : view.getsys_no()));
         lvtrParam.add((view.gettype_id()==null ? null : view.gettype_id()));
         lvtrParam.add((view.gettype_up()==null ? null : view.gettype_up()));
         strTypePath=getTypePathData(view.getsys_no(),view.gettype_up(),view.getflag(),
                                     view.getseqno(),view.gettype_id());
         lvtrParam.add(strTypePath);
         lvtrParam.add((view.gettype_name()==null ? null : view.gettype_name()));
         lvtrParam.add((view.gettype_desc()==null ? null : view.gettype_desc()));
         lvtrParam.add((view.gettype_dept()==null ? null : view.gettype_dept()));
         lvtrParam.add((view.gettype_mgr()==null ? null : view.gettype_mgr()));
         lvtrParam.add((view.gettype_stdate()==null ? null : view.gettype_stdate()));
         lvtrParam.add((view.gettype_eddate()==null ? null : view.gettype_eddate()));
         lvtrParam.add((view.gettype_status()==null ? null : view.gettype_status()));
         lvtrParam.add((view.getsec_lev()==null ? null : view.getsec_lev()));
         lvtrParam.add((view.getsec_ctl()==null ? null : view.getsec_ctl()));
         lvtrParam.add((view.getflag()==null ? null : view.getflag()));
         lvtrParam.add((view.getapp_empno()==null ? null : view.getapp_empno()));
         lvtrParam.add((view.getflow_send()==null ? null : view.getflow_send()));
         lvtrParam.add((view.getseqno()==null ? null : view.getseqno()));
         lvtrParam.add((view.geturl()==null ? null : view.geturl()));
         lvtrParam.add((view.gettype_name_e()==null ? null : view.gettype_name_e()));
         lstrSQL="insert into wkf_menu" +
                 " ( sys_no,type_id,type_up,type_path,type_name,type_desc," +
                 "   type_dept,type_mgr,type_stdate,type_eddate,type_status," +
                 "   sec_lev,sec_ctl,flag,app_empno,flow_send,seqno,url,type_name_e) values " +
                 " (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){;
            return "wkf_menu";
         }
         lvtrParam.clear();

         return "";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }

   String exeDelSQL(View view) throws Exception{
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      try{
         lvtrParam.add((view.getsys_no()==null ? null : view.getsys_no()));
         lvtrParam.add((view.gettype_id()==null ? null : view.gettype_id()));
         lvtrParam.add((view.gettype_up()==null ? null : view.gettype_up()));
         lstrSQL="delete wkf_menu where sys_no=? and type_id=? and type_up=? ";
         if(!gqbnDAO.executeSQL(lstrSQL,lvtrParam)){;
            return "wkf_menu";
         }
         lvtrParam.clear();

         return "OK!";
      }
      catch(Exception e){
         e.printStackTrace();
         throw new Exception(e);
      }
      finally{
         lvtrParam.clear();
      }
   }

   void exeDelSQL(View view,int pintSn){
      String lstrSQL=new String();
      try{
         //lstrSQL="delete from wkf_prg_right where prg_no='"+view.getprg_no().trim()+"' and group_no='"+view.getgroup_no_g()[pintSn]+"' ";
         lstrSQL="delete wkf_menu " +
                 " where sys_no='" + view.getsys_no_g()[pintSn] + "'" +
                 "   and type_id='" + view.gettype_id_g()[pintSn] + "'" +
                 "   and type_up='" + view.gettype_up_g()[pintSn] + "'";
         gqbnDAO.executeSQL(lstrSQL);
      } catch(Exception e){
         e.printStackTrace();
      }
   }

   boolean exeUpdSQL(View view,int pintSn){
      String lstrSQL=new String();
      Vector<Object> lvtrParam = new Vector<Object>();
      String strTypePath="";
      try{
         lvtrParam.add((view.geturl_g()[pintSn]==null ? null : view.geturl_g()[pintSn]));
         lvtrParam.add((view.getflag_g()[pintSn]==null ? null : view.getflag_g()[pintSn]));
         lvtrParam.add((view.getseqno_g()[pintSn]==null ? null : view.getseqno_g()[pintSn]));
         lvtrParam.add((view.gettype_name_g()[pintSn]==null ? null : view.gettype_name_g()[pintSn]));
         strTypePath=getTypePathData(view.getsys_no_g()[pintSn],view.gettype_up_g()[pintSn],view.getflag_g()[pintSn],
                                     view.getseqno_g()[pintSn],view.gettype_id_g()[pintSn]);
         lvtrParam.add(strTypePath);
         lvtrParam.add((view.gettype_name_e_g()[pintSn]==null ? null : view.gettype_name_e_g()[pintSn]));
         lvtrParam.add((view.gettype_status_g()[pintSn]==null ? null : view.gettype_status_g()[pintSn]));

         lstrSQL="update wkf_menu " +
                 "   set url=?,flag=?,seqno=?,type_name=?,type_path=?," +
                 "       type_name_e=?,type_status=? " +
                 " where sys_no='" + view.getsys_no_g()[pintSn] + "'" +
                 "   and type_id='" + view.gettype_id_g()[pintSn] + "'" +
                 "   and type_up='" + view.gettype_up_g()[pintSn] + "'";

         return gqbnDAO.executeSQL(lstrSQL,lvtrParam);
      }
      catch(Exception e){
         e.printStackTrace();
         return false;
      }
      finally{
         lvtrParam.clear();
      }
   }


   String batchUpdateData(View view) throws Exception{
       String[] lstrRowSn=view.getrowsn().split(",");

       try{
           for(int i=0;i<lstrRowSn.length;i++){
               if(!exeUpdSQL(view,Integer.parseInt(lstrRowSn[i]))){
                   throw new Exception("update error");
               }
           }
           return "目錄儲存成功!";
       }
       catch(Exception e){
           return "目錄儲存失敗!!";
       }
   }




   void setDataViewForm(DataViewForm DataViewForm,View view,String pstrLoginUser) throws Exception{

       //StringBuffer stbCondition = new StringBuffer();
       StringBuffer stbPageActionURI = new StringBuffer("menu_tree_maintain.jsp?rowsn=");
       //rowSn

       DataViewForm.setFieldsList(" '刪',type_up,type_id,flag,seqno,type_status," +
                                 /* " case when type_status='C' then '目錄'" +
                                  "      when type_status='P' then '程式'" +
                                  "      when type_status='O' then '外掛'" +
                                  "      when type_status='D' then '文件'" +
                                  "      when type_status='N' then '停用' end type_status," +*/
                                  " type_name,URL,sys_no,type_name_e");
       DataViewForm.setTablesList(" wkf_menu ");
       DataViewForm.setSQLCondition("sys_no='" + view.getsys_no() + "' ");
       DataViewForm.setOrderByFields("type_path");
       DataViewForm.setOrderBySeq("asc");
       DataViewForm.setGroupBy(null);
       DataViewForm.setHaving(null);

       String strColRelation[][] = new String[11][];

       strColRelation[0] = new String[5];
       strColRelation[1] = new String[5];
       strColRelation[2] = new String[8];
       strColRelation[3] = new String[5];
       strColRelation[4] = new String[5];
       strColRelation[5] = new String[5];
       strColRelation[6] = new String[5];
       strColRelation[7] = new String[5];
       strColRelation[8] = new String[5];
       strColRelation[9] = new String[5];
       strColRelation[10] = new String[5];

       strColRelation[0][0]="''";
       strColRelation[0][1]="INPUT";
       strColRelation[0][2]="WIDTH='5%' align='center'";
       //strColRelation[0][3]="<input type='button' CLASS='button b_normal' name='sel'  onclick=\"javascript:retVal(%sn%);\" value='刪'";
       strColRelation[0][3] ="<INPUT class='button b_normal' TYPE=\"BUTTON\" onclick=\"javascript:saveRowSn('%sn%');document.all.item('prgAction').value='delete';document.forms[0].submit();\" NAME=\"del\" VALUE=";
       strColRelation[0][4]="0";

       strColRelation[1][0]="'上層編號'";
       strColRelation[1][1]="INPUT";
       strColRelation[1][2]="WIDTH='6%'";
       strColRelation[1][3]="<INPUT TYPE='text' size='6' name='type_up_g' readonly CLASS='input_noborder' VALUE=";
       strColRelation[1][4]="1";

/*       strColRelation[2][0]="'程式編號'";
       strColRelation[2][1]="INPUT";
       strColRelation[2][2]="WIDTH='6%'";
       strColRelation[2][3]="<INPUT TYPE='text' size='6' name='type_id%sn%' readonly CLASS='input_noborder' VALUE=";
       strColRelation[2][4]="2";*/

       strColRelation[2][0]="'程式編號'";
       strColRelation[2][1]="LINK";
       strColRelation[2][2]=" nowrap WIDTH='6%' ";
       strColRelation[2][3]="<a target='_blank' href=";
       strColRelation[2][4]="2";
       strColRelation[2][5]="'/SWD/pages/maintain/project/grpprg_right_maintain.jsp'";
       strColRelation[2][6]="prg_no=";
       strColRelation[2][7]="2";


       strColRelation[3][0]="'層級'";
       strColRelation[3][1]="INPUT";
       strColRelation[3][2]="WIDTH='2%' align='center'";
//       strColRelation[3][3]="<INPUT TYPE='text' size='2' name='flag%sn%' readonly CLASS='input_noborder' VALUE=";
       strColRelation[3][3]="<INPUT TYPE='text' size='2' name='flag_g' CLASS='input' onchange=\"javascript:saveRowSn('%sn%');\" VALUE=";
       strColRelation[3][4]="3";

       strColRelation[4][0]="'序號'";
       strColRelation[4][1]="INPUT";
       strColRelation[4][2]="WIDTH='2%' align='center'";
//       strColRelation[4][3]="<INPUT TYPE='text' size='2' name='seqno%sn%' readonly CLASS='input_noborder' VALUE=";
       strColRelation[4][3]="<INPUT TYPE='text' size='2' name='seqno_g' CLASS='input' onchange=\"javascript:saveRowSn('%sn%');\" VALUE=";
       strColRelation[4][4]="4";

       strColRelation[5][0]="'種類'";
       strColRelation[5][1]="SELECT";
       strColRelation[5][2]="WIDTH='10%' align='center'";
       strColRelation[5][3]="<SELECT CLASS='select' NAME='type_status_g' onchange=\"javascript:saveRowSn('%sn%');\">";
       strColRelation[5][4]="<option value=\"C\">目錄</option>," +
                            "<option value=\"P\">程式</option>," +
                            "<option value=\"O\">外掛</option>," +
                            "<option value=\"D\">文件</option>," +
                            "<option value=\"N\">停用</option>";
       /*strColRelation[5][0]="'種類'";
       strColRelation[5][1]="INPUT";
       strColRelation[5][2]="WIDTH='4%' ";
       strColRelation[5][3]="<INPUT TYPE='text' size='3' name='type_status%sn%' readonly CLASS='input_noborder' VALUE=";
       strColRelation[5][4]="5";*/


       strColRelation[6][0]="'程式中文名稱'";
       strColRelation[6][1]="INPUT";
       strColRelation[6][2]="WIDTH='15%' ";
//       strColRelation[6][3]="<INPUT TYPE='text' size='15' name='type_name%sn%' readonly CLASS='input_noborder' VALUE=";
       strColRelation[6][3]="<INPUT TYPE='text' size='15' name='type_name_g' CLASS='input' onchange=\"javascript:saveRowSn('%sn%');\" VALUE=";
       strColRelation[6][4]="6";

       strColRelation[7][0]="'URL'";
       strColRelation[7][1]="INPUT";
       strColRelation[7][2]="WIDTH='35%' ";
//       strColRelation[7][3]="<INPUT TYPE='text' size='45' name='url%sn%' readonly CLASS='input_noborder' VALUE=";
       strColRelation[7][3]="<INPUT TYPE='text' size='45' name='url_g' CLASS='input' onchange=\"javascript:saveRowSn('%sn%');\" VALUE=";
       strColRelation[7][4]="7";

       strColRelation[8][0]="HIDDEN";
       strColRelation[8][1]="INPUT";
       strColRelation[8][2]="WIDTH='0%'";
       strColRelation[8][3]="<input type='hidden' name='sys_no_g' value=";
       strColRelation[8][4]="8";

       strColRelation[9][0]="'程式英文名稱'";
       strColRelation[9][1]="INPUT";
       strColRelation[9][2]="WIDTH='15%' ";
       strColRelation[9][3]="<INPUT TYPE='text' size='20' name='type_name_e_g' CLASS='input' onchange=\"javascript:saveRowSn('%sn%');\" VALUE=";
       strColRelation[9][4]="9";


       strColRelation[10][0]="HIDDEN";
       strColRelation[10][1]="INPUT";
       strColRelation[10][2]="WIDTH='0%'";
       strColRelation[10][3]="<INPUT TYPE='hidden' name='type_id_g' VALUE=";
       strColRelation[10][4]="2";


       DataViewForm.setColRelation(strColRelation);//將欄位相關設定殖入ActionForm
       DataViewForm.setJumpPageButtonShow(false);   //是否要秀出上下頁按鈕
       DataViewForm.setPageBtnOnTop(false);         //將button and rows info 秀在頂端
       DataViewForm.setPageBtnOnButtom(true);      //將button and rows info 秀在底端
       DataViewForm.setFilterShow(false);           //是否要秀出過濾選擇欄位
       DataViewForm.setJumpPageShow(false);         //是否要秀出跳頁下拉式選單
       DataViewForm.setRowsInfoShow(false);         //是否要秀出筆數資訊
       DataViewForm.setRowPageShow(false);          //是否要秀出每頁筆數控制欄
       DataViewForm.setExcelShow(false);           //是否要秀出轉Excel button
       DataViewForm.setOrderEnable(false);          //是否要提供排序點選功能
       DataViewForm.setShowHeaderWhenNoData(false);//當ResultSet無資料是否要秀Header

       DataViewForm.setTableHeaderHtml("class='td_head_grid' align='center'  width='680px'");
             //設定上一頁與下一頁按鈕的form action(通常為母頁即本頁)
       DataViewForm.setPageActionURI(stbPageActionURI.toString());
   }

   String getFlagData(View view) throws Exception{
      String strFlag="";
      if (view.gettype_up().equals("foldersTree")){
         strFlag = "1";
      } else {
         strFlag=gqbnDAO.readSingleValueData("select flag + 1 from wkf_menu " +
                                             " where sys_no='" + view.getsys_no() + "' " +
                                             "   and type_id='" + view.gettype_up() + "' " +
                                             "   and type_status='C'");
      }
      view.setflag(strFlag);
      return strFlag;
   }

   String checkHaveMenu(String strSysno) throws Exception{
       // 檢查是否已有人資料
       String strQty="";
       strQty=(gqbnDAO.readSingleValueData("select count(*) from wkf_menu" +
                                           " where sys_no='" + strSysno + "'" +
                                           "   and type_status='C'" ));
       if (strQty.equals("0")) {
          return "N";
       } else {
          return "Y";
       }
   }

   String getSysCount(String strSysno) throws Exception{
       // 檢查是否已有人資料
       String strQty="";
       strQty=(gqbnDAO.readSingleValueData("select count(*) from wkf_menu" +
                                           " where sys_no='" + strSysno + "'"));
       return strQty;
   }

   String getTypePathData(String strSysNo,String strTypeUp,String strFlag,
                            String strSeqno,String strTypeID) throws Exception{
      String strTypePath="";
      Hashtable<String,Hashtable<String,Object>> menuData=null;
      String menuData_sql = "select * from (" +
                            "  SELECT trim(to_char(flag,'09')) || ',' || seqno || ',' || type_id my_path," +
                            "         type_up,type_id" +
                            "    FROM wkf_menu " +
                            "   WHERE sys_no ='" + strSysNo + "'" +
                            "   ORDER BY flag,seqno) " +
                            " start with type_id = '" + strTypeUp + "' connect by prior type_up =  type_id";

      strTypePath="0" + gqbnDAO.readSingleValueData("select flag+1 from wkf_menu where type_id='" + strTypeUp + "'") + "," + strSeqno + "," + strTypeID;
      //System.out.println("1:" + strTypePath);

      menuData = gqbnDAO.hashtableSQL(menuData_sql);
      if (menuData != null && menuData.size() > 0) {
        for (int i = 0; i < menuData.size(); i++) {
           strTypePath = ((java.util.Hashtable<String,Object>)menuData.get(String.valueOf(i))).get("my_path".toUpperCase()).toString() +
                         "/" + strTypePath;
           //System.out.println("2:" + strTypePath);
        }
      }

      strTypePath = "00,0000,foldersTree/" + strTypePath;
      //System.out.println("3:" + strTypePath);
      return strTypePath;
   }

   String checkNotNullFld(View view) throws Exception{
      StringBuffer lstbNullFldList=new StringBuffer();
      //lstbNullFldList.append(view.getsys_no().equals("0") ? "異常原因及權責單位設定" + "," : "");
      return (lstbNullFldList.toString().equals("") ? "" : lstbNullFldList.delete(lstbNullFldList.length()-1,lstbNullFldList.length()).toString());
   }
   void setInitValueForAdd(View view,String pstrUserID) throws Exception{
      view.settype_dept("06000000");
      view.settype_mgr("00513");
      view.settype_stdate("date");
      view.settype_eddate("date");
      view.setsec_lev("C");
      view.setsec_ctl("N");
      view.setapp_empno(pstrUserID);
      view.setflow_send("Y");
      view.setseqno("0001");
   }
   void setReadonlyForAdd(View view) throws Exception{
   }


}
